Skip to main content

User Management

User Management​

MySQL has a user management system to control who can access the database and what actions they can perform. This is part of database security, ensuring only authorized users can perform operations.

Creating Users in MySQL​

MySQL users are stored in the mysql.user table. Each user is identified by:

  • Username
  • Host (where they can connect from)

Syntax:

CREATE USER 'username'@'host' IDENTIFIED BY 'password';
  • 'username' → Name of the user.
  • 'host' → Hostname/IP the user can connect from.
    • 'localhost' → User can connect only from the same machine as the MySQL server.
    • '%' → Wildcard, allows connection from any host.
  • 'password' → Password for authentication.

Example:

CREATE USER 'john'@'localhost' IDENTIFIED BY 'securePass123';

This creates a user john who can connect only from localhost with password securePass123.

Granting Privileges​

Once a user is created, they usually don’t have any privileges. We assign permissions using the GRANT statement.

Syntax:

GRANT privilege_list ON database.table TO 'username'@'host';
  • privilege_list → Type of access (e.g., SELECT, INSERT, UPDATE, DELETE, ALL PRIVILEGES).
  • database.table → Scope of privileges.
    • *.* → All databases and tables.
    • mydb.* → All tables in database mydb.
    • mydb.employees → Only the employees table in mydb.

Grant all privileges on a specific database

GRANT ALL PRIVILEGES ON company.* TO 'john'@'localhost';

Grant limited privileges

GRANT SELECT ON company.* TO 'john'@'localhost';

Revoking Privileges​

If a user should no longer have some permissions, use the REVOKE statement.

Syntax:

REVOKE privilege_list ON database.table FROM 'username'@'host';

Example:

REVOKE INSERT ON company.employees FROM 'john'@'localhost';

John can still read (SELECT) the employees table, but can no longer INSERT new rows.

Viewing User Privileges​

To check what privileges a user has:

SHOW GRANTS FOR 'john'@'localhost';

Example Output:

GRANT USAGE ON *.* TO 'john'@'localhost' IDENTIFIED BY PASSWORD '*HASH'
GRANT SELECT ON `company`.`employees` TO 'john'@'localhost'

Dropping Users (if no longer needed)​

DROP USER 'john'@'localhost';

Completely removes the user and their privileges.